package com.zhiyou.app.shop.website.dao.impl;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate4.HibernateCallback;
import org.springframework.stereotype.Component;

import com.zhiyou.app.shop.manager.dao.impl.BaseHibernateDAO;
import com.zhiyou.app.shop.website.dao.IUserAccountDao;
import com.zhiyou.app.shop.website.dao.pojo.User;
import com.zhiyou.app.shop.website.dao.pojo.UserAccount;

@Component
public class UserAccountDaoImpl extends BaseHibernateDAO<UserAccount, String> implements IUserAccountDao {
	
	public UserAccountDaoImpl(){
		super(UserAccount.class);
	}

	public UserAccount findByAccount(final String userAccount) {
		UserAccount list = hibernateTemplate.execute(new HibernateCallback<UserAccount>() {
			public UserAccount doInHibernate(Session session)
					throws HibernateException {
				String hql = "from UserAccount where userId = :userAccount";
				Query query = session.createQuery(hql);
				Map<String, Object> param = new HashMap<String, Object>();
				param.put("userAccount", userAccount);
				query.setProperties(param);
				List<UserAccount> list = query.list();
				if(list != null && list.size() > 0){
					return list.get(0);
				}
				return null;
			}
		});
		return list;
	}

	public List find(final User user, final String curType, final String operType,
			final String quantity, final int startNum, final int pageSize) {
		List list = hibernateTemplate.execute(new HibernateCallback<List>() {
			public List doInHibernate(Session session)
					throws HibernateException {
			String sql = "select u.user_account,(select name from dic_item where type='level' and code= u.level)," +
					"u.level,u.activity_date,ua.activ_money,ua.bonus_money,ua.stock_money,ua.consumer_money," +
					"ua.lottery_balance,ua.addition_money,ua.stock_num,ua.stock_sale_num,ua.stock_buy_num  " +
					"from user u,user_account ua where u.user_account=ua.user_id ";
			if(user != null){
				if(user.getUserAccount() != null && !"".equals(user.getUserAccount())){
					sql += " and u.user_account like '%"+user.getUserAccount()+"%'";
				}
				if(user.getRecommendId() != null && !"".equals(user.getRecommendId())){
					sql += " and u.recommend_id = '"+user.getRecommendId()+"'";
				}
				if(user.getParentId() != null && !"".equals(user.getParentId())){
					sql += " and u.parent_id = '"+user.getParentId()+"'";
				}
				if(user.getLevel() != null && !"".equals(user.getLevel())){
					sql += " and u.level ='"+user.getLevel()+"'";
				}
			}
			//激活1  奖金币2    股金3   加单4  重消 5  股票数量 6
			if(curType  != null && !"".equals(curType)){
				if("1".equals(curType)){ ///激活币
					if(">=".equals(operType)){  //及货币 》= 数量
						sql += " and ua.activ_money >= "+quantity;
					}else if("<=".equals(operType)){
						sql += " and ua.activ_money <= "+quantity;
					}
				}else if("2".equals(operType)){
					if(">=".equals(operType)){  //及货币 》= 数量
						sql += " and ua.bonus_money >= "+quantity;
					}else if("<=".equals(operType)){
						sql += " and ua.bonus_money <= "+quantity;
					}
				}else if("3".equals(operType)){
					if(">=".equals(operType)){  //及货币 》= 数量
						sql += " and ua.stock_money >= "+quantity;
					}else if("<=".equals(operType)){
						sql += " and ua.stock_money <= "+quantity;
					}
				}else if("4".equals(operType)){
					if(">=".equals(operType)){  //及货币 》= 数量
						sql += " and ua.addition_money >= "+quantity;
					}else if("<=".equals(operType)){
						sql += " and ua.addition_money <= "+quantity;
					}
				}else if("5".equals(operType)){
					if(">=".equals(operType)){  //及货币 》= 数量
						sql += " and ua.consumer_money >= "+quantity;
					}else if("<=".equals(operType)){
						sql += " and ua.consumer_money <= "+quantity;
					}
				}else if("6".equals(operType)){
					if(">=".equals(operType)){  //及货币 》= 数量
						sql += " and ua.stock_num >= "+quantity;
					}else if("<=".equals(operType)){
						sql += " and ua.stock_num <= "+quantity;
					}
				}
			}
			sql += " limit "+startNum+","+pageSize;
			SQLQuery query = session.createSQLQuery(sql);
			List list = query.list();
			return list;
			}
		});
		return list;
	}

	public int findCnt(final User user, final String curType,final  String operType,final String quantity) {
		Integer list = hibernateTemplate.execute(new HibernateCallback<Integer>() {
			public Integer doInHibernate(Session session)
					throws HibernateException {
				String sql = "select count(1) as cnt from user u,user_account ua where u.user_account=ua.user_id ";
				if(user != null){
					if(user.getUserAccount() != null && !"".equals(user.getUserAccount())){
						sql += " and u.user_account like '%"+user.getUserAccount()+"%'";
					}
					if(user.getRecommendId() != null && !"".equals(user.getRecommendId())){
						sql += " and u.recommend_id = '"+user.getRecommendId()+"'";
					}
					if(user.getParentId() != null && !"".equals(user.getParentId())){
						sql += " and u.parent_id = '"+user.getParentId()+"'";
					}
					if(user.getLevel() != null && !"".equals(user.getLevel())){
						sql += " and u.level ='"+user.getLevel()+"'";
					}
				}
				//激活1  奖金币2    股金3   加单4  重消 5  股票数量 6
				if(curType  != null && !"".equals(curType)){
					if("1".equals(curType)){ ///激活币
						if(">=".equals(operType)){  //及货币 》= 数量
							sql += " and ua.activ_money >= "+quantity;
						}else if("<=".equals(operType)){
							sql += " and ua.activ_money <= "+quantity;
						}
					}else if("2".equals(operType)){
						if(">=".equals(operType)){  //及货币 》= 数量
							sql += " and ua.bonus_money >= "+quantity;
						}else if("<=".equals(operType)){
							sql += " and ua.bonus_money <= "+quantity;
						}
					}else if("3".equals(operType)){
						if(">=".equals(operType)){  //及货币 》= 数量
							sql += " and ua.stock_money >= "+quantity;
						}else if("<=".equals(operType)){
							sql += " and ua.stock_money <= "+quantity;
						}
					}else if("4".equals(operType)){
						if(">=".equals(operType)){  //及货币 》= 数量
							sql += " and ua.addition_money >= "+quantity;
						}else if("<=".equals(operType)){
							sql += " and ua.addition_money <= "+quantity;
						}
					}else if("5".equals(operType)){
						if(">=".equals(operType)){  //及货币 》= 数量
							sql += " and ua.consumer_money >= "+quantity;
						}else if("<=".equals(operType)){
							sql += " and ua.consumer_money <= "+quantity;
						}
					}else if("6".equals(operType)){
						if(">=".equals(operType)){  //及货币 》= 数量
							sql += " and ua.stock_num >= "+quantity;
						}else if("<=".equals(operType)){
							sql += " and ua.stock_num <= "+quantity;
						}
					}
				}
				SQLQuery query = session.createSQLQuery(sql);
				Integer total = (Integer)query.addScalar("cnt", new org.hibernate.type.IntegerType()).uniqueResult(); 
				if(total == null){
					return 0;
				}
				return total;
			}
		});
		return list;
	}
	
	
	public void splitStock(final float per){
		hibernateTemplate.execute(new HibernateCallback<Object>() {
			public Object doInHibernate(Session session)
					throws HibernateException {
					String hql = "update UserAccount ua set ua.stockNum = ua.stockNum*"+per;
					session.createQuery(hql).executeUpdate();
					return null;
			}
		});
	}
	
	public List listSaleOrBuy(final String hql,final Map<String,Object> param,final int pageno,final int pagesize){
		List list = hibernateTemplate.execute(new HibernateCallback<List>() {
			public List doInHibernate(Session session)
					throws HibernateException {
				Query query = session.createQuery(hql);
				query.setProperties(param);
				query.setFirstResult((pageno-1)*pagesize);
				query.setMaxResults(pagesize);
				return query.list();
			}
		});
		return list;
	}

	public List<UserAccount> findByStock() {
		List<UserAccount> list = hibernateTemplate.execute(new HibernateCallback<List<UserAccount>>() {
			@SuppressWarnings("unchecked")
			public List<UserAccount> doInHibernate(Session session)
					throws HibernateException {
				final String hql = "from UserAccount ua where ua.stockMoney>0 ";
				Query query = session.createQuery(hql);
				return query.list();
			}
		});
		return list;
	}

	public BigDecimal getTotalAccount(final String account) {
		final String sql = "SELECT (IFNULL(ua.activ_money,0)+IFNULL(ua.bonus_money,0)+IFNULL(ua.stock_money,0)+IFNULL(ua.consumer_money,0)+IFNULL(ua.addition_money,0)+IFNULL(ua.recommend_money,0)) total FROM user_account ua WHERE ua.user_id=? ";
		BigDecimal list = hibernateTemplate.execute(new HibernateCallback<BigDecimal>() {
			public BigDecimal doInHibernate(Session session)
					throws HibernateException {
				SQLQuery query = session.createSQLQuery(sql);
				query.setString(0, account);
				BigDecimal total = (BigDecimal)query.addScalar("total", new org.hibernate.type.BigDecimalType()).uniqueResult(); 
				if(total == null){
					return BigDecimal.ZERO;
				}
				return total;
			}
		});
		return list;
	}
}
